package com.engc.common.dao.impl;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;

import com.engc.common.cache.CacheManager;
import com.engc.common.dao.BaseDao;
import com.engc.common.dao.DBExecuteException;

public class BaseDaoImpl implements BaseDao {
	private JdbcTemplate jt;
	private static final int defaultCount = 10; // 默认分页大小
	private static final boolean isshowSql = true;
	private static final Log logger = LogFactory.getLog(BaseDaoImpl.class);
	private DataSource dataSource;

	@Resource
	public void setJt(JdbcTemplate jt) {
		this.jt = jt;
	}

	@Resource
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	private String getExecuteSql(String sql, Object... parameters) {
		StringBuilder sb = new StringBuilder();
		String[] sqls = sql.split("\\?");
		for (int i = 0; i < sqls.length; i++) {
			if (sql.endsWith("?") || i != sqls.length - 1) {
				sb.append(sqls[i]).append("'").append(getParameter(i, parameters)).append("'");
			} else {
				sb.append(sqls[i]);
			}
		}
		return sb.toString();
	}

	private static Object getParameter(int i, Object[] parameters) {
		Object obj;
		if (i < parameters.length) {
			obj = parameters[i];
		} else {
			obj = "?";
		}
		return obj;
	}

	private Object[] getParameter(Object pojo) throws Exception {
		Field[] fields = this.getFields(pojo.getClass());
		Object[] obj = new Object[fields.length];
		for (int i = 0; i < fields.length; i++) {
			Field field = fields[i];
			field.setAccessible(true);
			obj[i] = field.get(pojo);
		}
		return obj;
	}

	private Object[] getParameter(Object pojo, String... columns) throws Exception {
		Field[] fields = this.getFields(pojo.getClass(), columns);
		Object[] obj = new Object[fields.length];
		for (int i = 0; i < fields.length; i++) {
			Field field = fields[i];
			field.setAccessible(true);
			obj[i] = field.get(pojo);
		}
		return obj;
	}

	@Override
	public int Insert(String tableName, Object pojo) throws Exception {
		String sql = this.getInsertSQL(tableName, pojo.getClass());
		return this.execute(sql, getParameter(pojo));
	}

	@Override
	public int Insert(String tableName, Object pojo, String... column) throws Exception {
		String sql = this.getInsertSQL(tableName, pojo.getClass(), column);
		return this.execute(sql, this.getParameter(pojo, column));
	}

	@Override
	public int Insert(String tableName, Map<String, Object> record) throws Exception {
		String sql = getRecordInsertSQL(record, tableName);
		Object[] keyset = record.keySet().toArray();
		Object[] objs = new Object[keyset.length];
		for (int i = 0; i < keyset.length; i++) {
			objs[i] = record.get(keyset[i]);
		}
		return this.execute(sql, objs);
	}

	private String getRecordInsertSQL(Map<String, Object> record, String tableName) {
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO ");
		sql.append(" " + tableName + " ");
		sql.append(" (");
		Set<String> columnf = record.keySet();
		Iterator it = columnf.iterator();
		while (it.hasNext()) {
			sql.append(" ").append(it.next()).append(" ").append(",");
		}
		sql.deleteCharAt(sql.lastIndexOf(","));
		sql.append(")");
		sql.append(" VALUES(");
		it = columnf.iterator();
		while (it.hasNext()) {
			it.next();
			// if(record.get(it.next()) instanceof java.util.Date){
			// sql.append("to_date(?,'yyyy-mm-dd hh24:mi:ss')");
			// }else{
			// sql.append("?");
			// }
			sql.append("?,");
		}
		sql.deleteCharAt(sql.lastIndexOf(","));
		sql.append(") ");
		return sql.toString();
	}

	@Override
	public int Insert(String sql, Object... parameter) throws Exception {
		return this.execute(sql, parameter);
	}

	@Override
	public int Update(String tableName, Object pojo, String where, Object... parameter) throws Exception {
		String sql = this.getUpdateSQL(tableName, pojo.getClass(), where);
		Object[] objs = this.getParameter(pojo);
		Object[] tempParameter = new Object[objs.length + parameter.length];
		System.arraycopy(objs, 0, tempParameter, 0, objs.length);
		System.arraycopy(parameter, 0, tempParameter, objs.length, parameter.length);
		return this.Update(sql, tempParameter);
	}

	@Override
	public int Update(String tableName, Object pojo, String where, String[] column, Object... parameter) throws Exception {
		String sql = this.getUpdateSQL(tableName, pojo.getClass(), where, column);
		Object[] objs = this.getParameter(pojo, column);
		Object[] tempParameter = new Object[objs.length + parameter.length];
		System.arraycopy(objs, 0, tempParameter, 0, objs.length);
		System.arraycopy(parameter, 0, tempParameter, objs.length, parameter.length);
		return this.Update(sql, tempParameter);
	}

	@Override
	public int Update(String tableName, Map<String, Object> record, String where, Object... parameter) throws Exception {
		String sql = getRecordUpdateSQL(record, tableName, where);
		Object[] keyset = record.keySet().toArray();
		Object[] objs = new Object[keyset.length + parameter.length];
		for (int i = 0; i < keyset.length; i++) {
			objs[i] = record.get(keyset[i]);
		}
		System.arraycopy(parameter, 0, objs, keyset.length, parameter.length);
		return this.Update(sql, objs);
	}

	private String getRecordUpdateSQL(Map<String, Object> record, String tableName, String sWhere) {
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE ");
		sql.append(" " + tableName + "  ");
		sql.append(" SET ");
		Set<String> columnf = record.keySet();
		Iterator<String> it = columnf.iterator();
		while (it.hasNext()) {
			Object myfield = it.next();
			sql.append(" ").append(myfield).append(" ").append("=?").append(",");
			
		}
		sql.deleteCharAt(sql.lastIndexOf(","));
		if (sWhere != null && !"".equals(sWhere)) {
			sql.append(" where ");
			sql.append(sWhere);
		} else {
			sql.append(" where 1=2");
		}
		return sql.toString();
	}

	@Override
	public int Update(String sql, Object... parameter) throws Exception {
		if (isshowSql) {
			logger.info("[SQL] >>>" + getExecuteSql(sql, parameter));
		}
		return jt.update(sql, parameter);
	}

	@Override
	public int Delete(String tableName, String where, Object... parameter) throws Exception {
		String sql = this.getDeleteSQL(tableName, where);
		return this.execute(sql, parameter);
	}

	@Override
	public int Delete(String sql, Object... parameter) throws Exception {
		return this.execute(sql, parameter);
	}

	@Override
	public Map<String, Object> Read(String sql, Object... parameter) {
		if (isshowSql) {
			logger.info("[SQL] >>>" + getExecuteSql(sql, parameter));
		}
		List<Map<String, Object>> templst = jt.query(sql, parameter, new baseMapper());
		return templst == null || templst.isEmpty() ? new HashMap<String, Object>() : templst.get(0);
	}

	@Override
	public <T> T Read(Class<T> clazz, String sql, Object... parameter) {
		if (isshowSql) {
			logger.info("[SQL] >>>" + getExecuteSql(sql, parameter));
		}
		RowMapper<T> rm = ParameterizedBeanPropertyRowMapper.newInstance(clazz);
		return jt.query(sql, parameter, rm).get(0);
	}

	@Override
	public List<Map<String, Object>> Query(String sql, Object... parameter) {
		if (isshowSql) {
			logger.info("[SQL] >>>" + getExecuteSql(sql, parameter));
		}
		List<Map<String, Object>> templst = jt.query(sql, parameter, new baseMapper());
		return templst;
	}

	@Override
	public <T> List<T> Query(Class<T> clazz, String sql, Object... parameter) {
		if (isshowSql) {
			logger.info("[SQL] >>>" + getExecuteSql(sql, parameter));
		}
		RowMapper<T> rm = ParameterizedBeanPropertyRowMapper.newInstance(clazz);
		return jt.query(sql, parameter, rm);
	}

	@Override
	public <T> List<T> Query_SliceOracle(Class<T> beanClass, String sql, int page, int pagesize, Object... parameter) {
		try {
			if (page < 0 || pagesize < 0) {
				throw new IllegalArgumentException("the parameter of 'page' or 'count',Must be positive. ");
			}
			pagesize = (pagesize > 0) ? pagesize : defaultCount;
			int from = (page - 1) * pagesize;
			ArrayList<Object> temppar = new ArrayList<Object>();
			List<Object> tpars = Arrays.asList(parameter == null ? new Object[] {} : parameter);
			temppar.addAll(tpars);
			temppar.add(from);
			temppar.add(from + pagesize);
			Object[] parameters = temppar.toArray();
			sql = "SELECT * FROM (SELECT ROWNUM RN, res.* FROM (" + sql + ") res WHERE ROWNUM <=?) WHERE RN >?";
			if (isshowSql) {
				logger.info("[SQL] >>>" + getExecuteSql(sql, parameters));
			}
			RowMapper<T> rm = ParameterizedBeanPropertyRowMapper.newInstance(beanClass);
			return jt.query(sql, parameters, rm);
		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList<T>();
		}

	}

	/**
	 * 执行统计查询语句，语句的执行结果必须只返回一个数值
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public int getTotal(String tableName, String sWhere, Object... params) {
		StringBuffer sbuffer = new StringBuffer();
		sbuffer.append("select count(1) from ");
		sbuffer.append(" " + tableName + "  ");
		if (sWhere != null && !"".equals(sWhere)) {
			sbuffer.append(" where ");
			sbuffer.append(sWhere);
		}
		if (isshowSql) {
			logger.info("[SQL] >>>" + getExecuteSql(sbuffer.toString(), params));
		}
		return jt.queryForInt(sbuffer.toString(), params);
	}

	public int getTotal(String sql, Object... parameter) {
		String sqlstr = "select count(1) from ( " + sql + " )";
		if (isshowSql) {
			logger.info("[SQL] >>>" + getExecuteSql(sqlstr, parameter));
		}
		return jt.queryForInt(sqlstr, parameter);
	}

	
	public  int executeCountSql(String sql , Object... parameter){
		
		if (isshowSql) {
			logger.info("[SQL] >>>" + getExecuteSql(sql, parameter));
		}
		return jt.queryForInt(sql, parameter);
	}
	
	
	@Override
	public <T> List<T> Query_SliceMySql(Class<T> beanClass, String sql, int page, int pagesize, Object... parameter) {
		try {
			if (page < 0 || pagesize < 0) {
				throw new IllegalArgumentException("the parameter of 'page' or 'count',Must be positive. ");
			}
			pagesize = (pagesize > 0) ? pagesize : defaultCount;
			int from = (page - 1) * pagesize;
			ArrayList<Object> temppar = new ArrayList<Object>();
			List<Object> tpars = Arrays.asList(parameter == null ? new Object[] {} : parameter);
			temppar.addAll(tpars);
			temppar.add(from);
			temppar.add(from + pagesize);
			Object[] parameters = temppar.toArray();
			if (isshowSql) {
				logger.info("[SQL] >>>" + getExecuteSql(sql + " LIMIT ?,?", parameters));
			}
			RowMapper<T> rm = ParameterizedBeanPropertyRowMapper.newInstance(beanClass);
			return jt.query(sql + " LIMIT ?,? ", parameters, rm);
		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList<T>();
		}
	}

	@Override
	public List<Map<String, Object>> Query_SliceOracle(String sql, int page, int pagesize, Object... parameter) {
		try {
			if (page < 0 || pagesize < 0) {
				throw new IllegalArgumentException("the parameter of 'page' or 'count',Must be positive. ");
			}
			pagesize = (pagesize > 0) ? pagesize : defaultCount;
			int from = (page - 1) * pagesize;
			ArrayList<Object> temppar = new ArrayList<Object>();
			List<Object> tpars = Arrays.asList(parameter == null ? new Object[] {} : parameter);
			temppar.addAll(tpars);
			temppar.add(from + pagesize);
			temppar.add(from);
			Object[] parameters = temppar.toArray();
			sql = "SELECT * FROM (SELECT ROWNUM RN, res.* FROM (" + sql + ") res WHERE ROWNUM <=?) WHERE RN >?";
			if (isshowSql) {
				logger.info("[SQL] >>>" + getExecuteSql(sql, parameters));
			}
			return jt.query(sql, parameters, new baseMapper());
		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList<Map<String, Object>>();
		}
	}

	@Override
	public List<Map<String, Object>> Query_SliceMySql(String sql, int page, int pagesize, Object... parameter) {
		try {
			if (page < 0 || pagesize < 0) {
				throw new IllegalArgumentException("the parameter of 'page' or 'count',Must be positive. ");
			}
			pagesize = (pagesize > 0) ? pagesize : defaultCount;
			int from = (page - 1) * pagesize;
			ArrayList<Object> temppar = new ArrayList<Object>();
			List<Object> tpars = Arrays.asList(parameter == null ? new Object[] {} : parameter);
			temppar.addAll(tpars);
			//temppar.add(from);
			//temppar.add(from + pagesize);
			Object[] parameters = temppar.toArray();
			if (isshowSql) {
				logger.info("[SQL] >>>" + getExecuteSql(sql + " LIMIT 0,20",parameters));
			}
			return jt.queryForList(sql + " LIMIT 0,20 ", parameters, new baseMapper());
		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList<Map<String, Object>>();
		}
	}

	@Override
	public Map<String, Object> Read_Cache(String cacheName, Serializable key, String sql, Object... params) {
		Map<String, Object> objs = (Map<String, Object>) CacheManager.get(cacheName, key);
		if (objs == null) {
			objs = Read(sql, params);
			CacheManager.set(cacheName, key, (Serializable) objs);
		}
		return objs;
	}

	@Override
	public <T> T Read_Cache(Class<T> beanClass, String cacheName, Serializable key, String sql, Object... params) {
		T objs = (T) CacheManager.get(cacheName, key);
		if (objs == null) {
			objs = Read(beanClass, sql, params);
			CacheManager.set(cacheName, key, (Serializable) objs);
		}
		return objs;
	}

	@Override
	public List<Map<String, Object>> Query_Cache(String cacheName, Serializable key, String sql, Object... params) {
		List<Map<String, Object>> objs = (List<Map<String, Object>>) CacheManager.get(cacheName, key);
		if (objs == null) {
			objs = Query(sql, params);
			CacheManager.set(cacheName, key, (Serializable) objs);
		}
		return objs;
	}

	@Override
	public <T> List<T> Query_Cache(Class<T> beanClass, String cacheName, Serializable key, String sql, Object... params) {
		List<T> objs = (List<T>) CacheManager.get(cacheName, key);
		if (objs == null) {
			objs = Query(beanClass, sql, params);
			CacheManager.set(cacheName, key, (Serializable) objs);
		}
		return objs;
	}

	private DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	/**
	 * 执行DML语句，該用JDBCTemplate，做事務控制
	 */
	@Override
	public int execute(String sql, final Object... pars) throws Exception {

		if (isshowSql) {
			logger.info("[SQL] >>>" + getExecuteSql(sql, pars));
		}
		return jt.update(sql, pars);
	}

	/**
	 * 得到插入SQL语句， 参数为表名， Bean对象的实例 props为需要插入的字段名数组, 为空时即更新全部
	 * 
	 * @param tableName
	 * @param obj
	 * @param props
	 * @return
	 */
	private String getInsertSQL(String tableName, Class obj) {
		return getInsertSQL(tableName, obj, null);
	}

	private String getInsertSQL(String tableName, Class obj, String... columns) {
		Field[] fields = null;
		fields = getFields(obj, columns);

		// 拼SQL语句
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO ");
		sql.append(" " + tableName + " ");
		sql.append(" (");
		for (int i = 0; i < fields.length; i++) {
			sql.append(" " + fields[i].getName() + " ");
			if (i < fields.length - 1) {
				sql.append(",");
			}
		}
		sql.append(") ");
		sql.append(" VALUES(");
		for (int i = 0; i < fields.length; i++) {
			// if(fields[i].getType()==java.util.Date.class){
			// sql.append("to_date(?,'yyyy-mm-dd hh24:mi:ss')");
			// }else{
			// sql.append("?");
			// }
			sql.append("?");
			if (i < fields.length - 1) {
				sql.append(",");
			}
		}
		sql.append(") ");
		return sql.toString();
	}

	/**
	 * 得到更新SQL语句， 参数为表名， Bean对象的实例， sWhere为搜索条件， 必须要有搜索条件 props为需要更新的字段名数组,
	 * 为空时即更新全部
	 * 
	 * @param tableName
	 * @param obj
	 * @param sWhere
	 * @return
	 */
	private String getUpdateSQL(String tableName, Class obj, String sWhere) {
		return getUpdateSQL(tableName, obj, sWhere, null);
	}

	
	public static void main(String[] args) {
		BaseDaoImpl d = new BaseDaoImpl();
//		SysUserInfo info = new SysUserInfo();
//		info.setIp("11");
//		info.setLogintime("fdfd");
//		System.out.println(d.getUpdateSQL("t", info.getClass(), ""));
//		Map<String,Object> record = new HashMap<String, Object>();
//		record.put("id", 1);
//		record.put("name", 2);
//		System.out.println(d.getRecordInsertSQL(record, "table"));
		
		
		
		Object[] objs = new Object[]{"11","22"};
		Object[] parameter = new Object[]{"33","44"}; 
		Object[] tempParameter = new Object[objs.length + parameter.length];
		
		
		
		
		System.arraycopy(objs, 0, tempParameter, 0, objs.length);
		System.out.println(Arrays.toString(tempParameter));
		System.arraycopy(parameter, 0, tempParameter, objs.length, parameter.length);
		System.out.println(Arrays.toString(tempParameter));
	}
	
	private String getUpdateSQL(String tableName, Class obj, String sWhere, String... props) {
		Field[] fields = null;
		fields = getFields(obj, props);
		// 拼SQL语句
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE ");
		sql.append(" " + tableName + "  ");
		sql.append(" SET ");
		for (int i = 0; i < fields.length; i++) {
			sql.append(" " + fields[i].getName() + "= ");
//			if (fields[i].getType() == java.util.Date.class) {
//				sql.append("to_date(?,'yyyy-mm-dd hh24:mi:ss')");
//			} else {
//				sql.append("?");
//			}
			sql.append("?");
			if (i < fields.length - 1) {
				sql.append(",");
			}
		}
		if (sWhere != null && !"".equals(sWhere)) {
			sql.append(" where ");
			sql.append(sWhere);
		} else {
			sql.append(" where 1=2");
		}
		return sql.toString();
	}

	/**
	 * 得到搜索SQL语句， 参数为表名， sWhere为搜索条件， 必须要有搜索条件
	 * 
	 * @param tableName
	 * @param sWhere
	 * @return
	 */
	private String getSelectSQL(String tableName, String sWhere) {
		// 拼SQL语句
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ");
		sql.append(" " + tableName + "  ");
		if (sWhere != null && !"".equals(sWhere)) {
			sql.append(" where ");
			sql.append(sWhere);
		}
		return sql.toString();
	}

	private String getWhereIn(Collection list) {
		StringBuffer where = new StringBuffer();
		for (int i = 0; i < list.size(); i++) {
			where.append("?,");
		}
		where.delete(where.length() - 1, where.length());
		return where.toString();
	}

	private Object[] getWhereInValue(Collection coll) {
		return coll.toArray();
	}

	/**
	 * 得到删除SQL语句， 参数为表名， sWhere为搜索条件， 必须要有搜索条件
	 * 
	 * @param tableName
	 * @param sWhere
	 * @return
	 */
	private String getDeleteSQL(String tableName, String sWhere) {
		// 拼SQL语句
		StringBuffer sql = new StringBuffer();
		sql.append("DELETE FROM ");
		sql.append(" " + tableName + "  ");
		if (sWhere != null && !"".equals(sWhere)) {
			sql.append(" where ");
			sql.append(sWhere);
		} else {
			sql.append(" where 1=2");
		}

		return sql.toString();
	}

	// 获取对象（bean）中的属性
	private Field[] getFields(Class obj) {
		return getFields(obj, null);
	}

	private Field[] getFields(Class obj, String[] s) {
		Field[] fields = null;
		Field[] tmp = null;
		// 获得所有的属性
		tmp = obj.getDeclaredFields();
		if (s != null) { // 是否选定部分参数
			fields = new Field[s.length];
			for (int j = 0; j < s.length; j++) {
				for (int i = 0; i < tmp.length; i++) {
					if (tmp[i].getName().equals(s[j])) {
						fields[j] = tmp[i];
					}
				}
			}
		} else { // 返回全部参数
			fields = tmp;
		}
		return fields;
	}

	/**
	 * 查询Sequence 最大值
	 * 
	 * @param seqName
	 * @return
	 */
	public String getSeqValue(String seqName) {
		String sql = "select " + seqName + ".Nextval as value from dual ";
		String value = jt.queryForObject(sql, String.class);
		return value;
	}

	protected class baseMapper implements RowMapper {
		public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
			Map<String, Object> u = new HashMap<String, Object>();
			ResultSetMetaData rsm = rs.getMetaData();
			int councol = rsm.getColumnCount();
			for (int i = 1; i <= councol; i++) {
				String columnName = rsm.getColumnLabel(i).toLowerCase();
				u.put(columnName, rs.getObject(i));
			}
			return u;
		}
	}
	
	
	
	
	
	
	
	
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public String executeCall(String sql, Object... params) throws DBExecuteException {

		final Object[] objarr = params;
		final String allsql = sql;	
		
		Object obj = this.jt.execute(new ConnectionCallback() {
			public Object doInConnection(Connection conn) throws SQLException, DataAccessException {

				CallableStatement cstmt = conn.prepareCall(allsql);

				cstmt.registerOutParameter(0, Types.VARCHAR);
				cstmt.setObject(1, objarr[0]);
				cstmt.setObject(2, objarr[1]);
				cstmt.setObject(3, objarr[2]);

				cstmt.execute();
				String out = cstmt.getString(0);
				cstmt.close();

				return out;
			}
		});
		if (obj != null && !obj.toString().trim().equals("")) {
			return obj.toString();
		}
		return null;

	}

}
